﻿using Asion.Dapper.SqlBuilder.Condition;
using Dapper;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Text;
using static Asion.Dapper.SqlMapperExtensions;

namespace Asion.Dapper.SqlBuilder
{
    public class SimpleCrudMySqlBuilder<TEntity> : AbstractSimpleCrudSqlBuilder<TEntity>, ISimpleCurdSqlBuilder<TEntity> where TEntity : IEntity
    {

        public SimpleCrudMySqlBuilder()
        {
            _encap_sulation = new MySqlEncapSulation();
        }

        private IEncapSulation _encap_sulation;

        public string EncapSulation(string name)
        {
            return _encap_sulation.EncapSulation(name);
        }



        public override string GetIdentitySql()
        {
            return string.Format("SELECT LAST_INSERT_ID() AS ID");
        }



        public override string GetPagedTemplate()
        {
            return "SELECT {SELECT_COLUMNS} FROM {TABLE_NAME} {WHERE_CLAUSE}  {ORDER_BY} LIMIT {OFFSET},{ROWS_PER_PAGE}";
        }


        #region insert part


        /// <summary>
        /// get insert sql
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public string GetInsertSql<Entity>(Entity entity, bool isCheckNull, List<string> excludes = null, bool isSelectId = false)
        {
            //get ids
            if (excludes == null)
            {
                excludes = new List<string>();
            }
            if (isCheckNull)
            {
                var nullEntity = AssemblyHelper.GetNullColumns(entity).ToList();
                excludes.AddRange(nullEntity);
            }

            //get insert columns
            var columns = this.Columns.Where(a => a.IsSupportInsert).ToList();

            //get id type
            Type idType = null;

            if (isSelectId)
            {
                idType = this.Columns.Where(a => a.IsKey).Select(a => a.ColumnType).ToList().FirstOrDefault();
            }

            var result = BuildInsertSql<Entity>(this.TableName, columns, excludes, idType);
            return result;
        }




        /// <summary>
        ///build insert sql
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name=""></param>
        private string BuildInsertSql<Entity>(string tableName, List<CustomColumnMapping> insertColumns, List<string> excludes, Type idType = null)
        {
            var sql = new StringBuilder();
            sql.AppendFormat("INSERT INTO {0}", EncapSulation(tableName));

            // get insert column
            sql.Append(" (");
            var paramsql = new StringBuilder();
            for (var i = 0; i < insertColumns.Count(); i++)
            {
                var property = insertColumns.ElementAt(i);
                //code 是null 还是包含
                if (ContainColumn(excludes, property) && !property.IsCode)
                    continue;
                paramsql.Append(EncapSulation(property.DataColumnName));
                if (i < insertColumns.Count() - 1)
                    paramsql.Append(", ");
            }
            if (paramsql.ToString().EndsWith(", "))
                paramsql.Remove(paramsql.Length - 2, 2);

            sql.Append(paramsql.ToString());
            sql.Append(") ");

            //get insert values
            sql.Append("VALUES");
            sql.Append(" (");
            var valueSql = new StringBuilder();
            for (var i = 0; i < insertColumns.Count(); i++)
            {
                var property = insertColumns.ElementAt(i);
                if (ContainColumn(excludes, property) && !property.IsCode)
                    continue;
                //简单的做法就是 code直接复制了不通过参数来处理了，以后在看如何灵活处理
                if (property.IsCode && excludes.Contains(property.ColumnName))
                {
                    valueSql.AppendFormat("'{0}'", DapperConfiguration.generate_code.GenerateCode());
                }
                else
                {
                    valueSql.AppendFormat("@{0}", property.ColumnName);
                }
                if (i < insertColumns.Count() - 1)
                    valueSql.Append(", ");
            }
            if (valueSql.ToString().EndsWith(", "))
                valueSql.Remove(valueSql.Length - 2, 2);
            sql.Append(valueSql.ToString());
            sql.Append(") ");


            if (idType != null)
            {

                if (idType == typeof(int) || idType == typeof(long))
                {
                    sql.Append(";" + GetIdentitySql());
                }
            }
            return sql.ToString();
        }

        #endregion

        #region delete part 

        /// <summary>
        /// get delete sql
        /// </summary>
        /// <param name="type"></param>
        /// <param name="where"></param>
        /// <param name="whereComandType"></param>
        /// <returns></returns>
        public string GetDeleteSql(IWhere where)
        {
            //GET TABLE INFO

            var result = BuildDeleteSql(this.TableName, where);
            return result;
        }




        /// <summary>
        /// build delete sql
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="where"></param>
        /// <param name="whereComandType"></param>
        /// <returns></returns>
        public string BuildDeleteSql(string tableName, IWhere where)
        {
            var sql = new StringBuilder();
            sql.AppendFormat("DELETE FROM {0}", EncapSulation(tableName));
            //GET WHERE
            var whereSql = where.ToSql(this.GetTEntityType());
            sql.Append(whereSql);
            return sql.ToString();
        }









        #endregion

        #region update part


        /// <summary>
        /// get update sql
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public virtual string GetUpdateSql<Entity>(Entity type, IUpdateWhere where, List<string> excludes = null)
        {

            //get ids
            if (excludes == null)
            {
                excludes = new List<string>();
            }
            var nullColumns = AssemblyHelper.GetNullColumns(type);
            excludes.AddRange(nullColumns);
            //get update columns
            var updateColumns = this.Columns.Where(a => a.IsSupportUpdate);
            if (!updateColumns.Any())
            {
                throw new Exception("Update must contain columns,please check column count or column values is null");
            }
            var conditon = where as IConditionDynamicParameters;

            var result = BuildUpdateSql(this.TableName, updateColumns, excludes, where);
            return result;
        }


        /// <summary>
        /// get update sql
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public virtual string GetBatchUpdateSql<Entity>(IBatchUpdate updateColumns, IWhere where)
        {
            var sql = new StringBuilder();
            sql.AppendFormat("UPDATE {0}", EncapSulation(_custom_table_mappinghandle.GetTableName()));
            //updateColumns.SetCount(type.Count);
            var setSql = updateColumns.ToSql(this.GetTEntityType());
            sql.AppendFormat(setSql);

            var whereSql = where.ToSql(this.GetTEntityType());
            sql.AppendFormat(whereSql);
            return sql.ToString();


        }


        public string GetUpdateSql<Entity>(IUpdateColumn column, IWhere where)
        {
            var keyHasPredefinedValue = false;
            var sql = new StringBuilder();
            sql.AppendFormat("UPDATE {0}", EncapSulation(_custom_table_mappinghandle.GetTableName()));

            var setSql = column.ToSql(this.GetTEntityType());
            sql.AppendFormat(setSql);

            var whereSql = where.ToSql(this.GetTEntityType());
            sql.AppendFormat(whereSql);
            Debug.WriteLine(sql);
            return sql.ToString();
        }

        /// <summary>
        /// 计数计算
        /// </summary>
        /// <typeparam name="Entity"></typeparam>
        /// <param name="column"></param>
        /// <param name="countType"></param>
        /// <param name="value"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public string GetUpdateCountSql<Entity>(string column, UpdateCountType countType, int value, IWhere where)
        {
            var keyHasPredefinedValue = false;
            var sql = new StringBuilder();
            sql.AppendFormat("UPDATE {0}", EncapSulation(_custom_table_mappinghandle.GetTableName()));

            var mapping = _custom_table_mappinghandle.GetColumnMapping(column);
            var formulate = countType == UpdateCountType.Increase ? "+" : "-";
            sql.AppendFormat($" SET {EncapSulation(mapping.DataColumnName)} ={mapping.DataColumnName}{formulate}{value}");

            var whereSql = where.ToSql(this.GetTEntityType());
            sql.AppendFormat(whereSql);
            Debug.WriteLine(sql);
            return sql.ToString();
        }


        /// <summary>
        ///build update sql
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name=""></param>
        private string BuildUpdateSql(string tableName, IEnumerable<CustomColumnMapping> setColumns, List<string> excludes, IUpdateWhere where)
        {
            var keyHasPredefinedValue = false;
            var sql = new StringBuilder();
            sql.AppendFormat("UPDATE {0}", EncapSulation(tableName));

            // GET UPDATE SET
            sql.AppendFormat(" SET ");
            for (var i = 0; i < setColumns.Count(); i++)
            {

                var property = setColumns.ElementAt(i);
                if (ContainColumn(excludes, property))
                    continue;
                if (property.IsKey)
                    continue;
                sql.AppendFormat("{0} = @{1}", property.DataColumnName, property.ColumnName);
                sql.AppendFormat(", ");
            }
            if (sql.ToString().EndsWith(", "))
                sql.Remove(sql.Length - 2, 2);

            // GET UPDATE WHERE
            var whereSql = where.ToSql(this.GetTEntityType());
            sql.AppendFormat(whereSql);

            return sql.ToString();
        }





        #endregion


        #region save part

        #endregion

        #region  select part

        /// <summary>
        /// get select sql by where
        /// </summary>
        /// <param name="type"></param>
        /// <param name="where">where includes cloumn and value </param>
        /// <returns></returns>
        public string GetSelectSql(ISelectColumn column, IWhere where, IOrderBy order = null, IGroupColumn group = null, int topCount = 0)
        {
            //get ids
            var tablename = this.TableName;

            if (column == null)
            {
                column = new AllSelectColumn();
            }


            var sql = BuildSelectSql(tablename, column, where, order: order, group: group, topCount: topCount);

            return sql;
        }


        /// <summary>
        /// page query
        /// </summary>
        /// <param name="type"></param>
        /// <param name="pageNumber"></param>
        /// <param name="rowsPerPage"></param>
        /// <param name="conditions"></param>
        /// <param name="orderby"></param>
        /// <param name="parameters"></param>
        /// <returns>page sql+page count sql</returns>
        public string GetSelectPagedSql(ISelectColumn column, IWhere where, int pageNumber = 1, int rowsPerPage = DapperConfiguration.default_rows_page, IOrderBy order = null)
        {

            // get page sql template
            var pagesql = GetPagedTemplate();
            var tablename = this.TableName;

            //get select
            var columnsSql = column.ToSql(this.GetTEntityType());

            //get where
            var whereSql = where.ToSql(this.GetTEntityType());

            //get orderby if orderby is null using  order by ids
            string orderBySql = string.Empty;
            if (order != null)
            {
                orderBySql = order.ToSql(this.GetTEntityType());
            }
            pagesql = pagesql.Replace("{SELECT_COLUMNS}", columnsSql);
            pagesql = pagesql.Replace("{TABLE_NAME}", tablename);
            //pagesql = pagesql.Replace("{PAGE_NUMBER}", pageNumber.ToString());
            pagesql = pagesql.Replace("{ROWS_PER_PAGE}", rowsPerPage.ToString());
            if (string.IsNullOrWhiteSpace(orderBySql))
            {
                pagesql = pagesql.Replace("{ORDER_BY}", " order by " + this.TableMapping.KeyColumn);
            }
            else
            {
                pagesql = pagesql.Replace("{ORDER_BY}", orderBySql);
            }
            pagesql = pagesql.Replace("{WHERE_CLAUSE}", whereSql);
            pagesql = pagesql.Replace("{OFFSET}", ((pageNumber - 1) * rowsPerPage).ToString());


            var pagesqlCount = GetSelectCountSql(where);

            var result = $"{pagesql};{pagesqlCount}";
            return result;
        }





        /// <summary>
        /// Building Select Sql
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name=""></param>
        private string BuildSelectSql(string tableName, ISelectColumn column, IWhere where, IOrderBy order = null, IGroupColumn group = null, int topCount = 0)
        {
            var sb = new StringBuilder();
            sb.Append("SELECT ");
            var columnsSql = column.ToSql(this.GetTEntityType());
            sb.Append(columnsSql);
            sb.AppendFormat(" FROM {0}  ", EncapSulation(tableName));
            //GET WHERE
            var whereSql = where.ToSql(this.GetTEntityType());
            sb.Append(whereSql);
            if (group != null)
            {
                var groupBy = group.ToSql(this.GetTEntityType());
                sb.Append(groupBy);
            }
            if (order != null)
            {
                var orderby = order.ToSql(this.GetTEntityType());
                sb.Append(orderby);
            }
            if (topCount > 0)
            {
                sb.Append($" limit {topCount}");
            }
            return sb.ToString();
        }




        #endregion


        #region  select advanced part
        /// <summary>
        /// get select count(1) sql by where
        /// </summary>
        /// <param name="type"></param>
        /// <param name="where">where includes cloumn and value </param>
        /// <returns></returns>
        public virtual string GetSelectExistSql(IWhere where)
        {
            //get ids
            var sql = BuildSelectExistSql(this.TableName, where);

            return sql;
        }

        public string GetSelectExistSql(IUpdateWhere where)
        {

            var conditon = where as IConditionDynamicParameters;

            var sb = new StringBuilder();
            sb.Append("SELECT 1 ");
            sb.AppendFormat(" FROM {0}  ", EncapSulation(this.TableName));
            // GET UPDATE WHERE
            var whereSql = where.ToSql(this.GetTEntityType());
            sb.AppendFormat(whereSql);
            return sb.ToString();
        }

        /// <summary>
        /// Building Select Sql
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name=""></param>
        private string BuildSelectExistSql(string tableName, IWhere where)
        {
            var sb = new StringBuilder();
            sb.Append("SELECT 1 ");
            sb.AppendFormat(" FROM {0}  ", EncapSulation(tableName));
            //GET WHERE
            var whereSql = where.ToSql(this.GetTEntityType());
            sb.Append(whereSql);

            return sb.ToString();
        }

        /// <summary>
        /// get select count(1) sql by where
        /// </summary>
        /// <param name="type"></param>
        /// <param name="where">where includes cloumn and value </param>
        /// <returns></returns>
        public virtual string GetSelectCountSql(IWhere where)
        {
            //get ids
            var sql = BuildSelectCountSql(this.TableName, where);

            return sql;
        }


        /// <summary>
        /// Building Select Sql
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name=""></param>
        private string BuildSelectCountSql(string tableName, IWhere where)
        {
            var sb = new StringBuilder();
            sb.Append("SELECT COUNT(1) ");
            sb.AppendFormat(" FROM {0}  ", EncapSulation(tableName));
            //GET WHERE
            var whereSql = where.ToSql(this.GetTEntityType());
            sb.Append(whereSql);

            return sb.ToString();
        }
        #endregion











        private DynamicParameters BuildDynamicParameters(List<string> ids, object id)
        {
            var dynParms = new DynamicParameters();
            if (ids.Count == 1)
                dynParms.Add("@" + ids.First(), id);
            else
            {
                foreach (var prop in ids)
                    dynParms.Add("@" + prop, id.GetType().GetProperty(prop).GetValue(id, null));
            }
            return dynParms;
        }

        public string EncapCloumnValue(string name)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 是否包含列
        /// </summary>
        /// <param name="excludes"></param>
        /// <param name="mapping"></param>
        /// <returns></returns>
        private bool ContainColumn(IList<string> excludes, CustomColumnMapping mapping)
        {
            if (excludes == null)
            {
                return false;
            }
            if (excludes.Contains(mapping.ColumnName) || excludes.Contains(mapping.DataColumnName))
            {
                return true;
            }
            return false;
        }

        public string GetBatchUpdateSql<Entity>(List<Entity> type, IBatchUpdate updateColumns)
        {
            throw new NotImplementedException();
        }
    }


    public enum UpdateCountType
    {
        Increase,
        Decrease

    }
}
